class Project < ActiveRecord::Base
  has_many :stages,:dependent => :destroy

  validates_uniqueness_of :name
  validates_presence_of :name
  validates_presence_of :signed_on

  default_scope :order => 'projects.updated_at desc'

  def paying_stages
    Stage.find_all_by_pay_status_and_project_id('paying',id)
  end

  def paused_stages
    Stage.find_all_by_pay_status_and_project_id('paused',id)
  end

  def payed_stages(args = {})
    if args.empty?
      Stage.find_all_by_pay_status_and_project_id('payed',id)
    else
      Stage.all :conditions => [
        'pay_status = ? and project_id = ? and payed_on >= ? and payed_on <= ? ',
        'payed',
        id,
        args[:started_on],
        args[:end_on]
      ]
    end
  end

  def stages_by_pay_status(pay_status)
    Stage.find_all_by_pay_status_and_project_id(pay_status,id)
  end

  def payed_by_pay_status(pay_status)
    instance_eval "#{pay_status}_total_amount"
  end

  def shown?(pay_status)
    pay_status.blank? or instance_eval "#{pay_status}_total_amount > 0"
  end

  def reset_stat!
    self.payed_stages_count = Stage.find_all_by_project_id_and_pay_status(id,'payed').size
    self.paying_stages_count = Stage.find_all_by_project_id_and_pay_status(id,'paying').size
    self.paused_stages_count = Stage.find_all_by_project_id_and_pay_status(id,'paused').size

    self.payed_total_amount = Stage.sum(:should_be_payed, :conditions => {
        :project_id => id,
        :pay_status => 'payed'
      }
    )
    self.paying_total_amount = Stage.sum(:should_be_payed, :conditions => {
        :project_id => id,
        :pay_status => 'paying'
      }
    )
    self.paused_total_amount = Stage.sum(:should_be_payed, :conditions => {
        :project_id => id,
        :pay_status => 'paused'
      }
    )
  end

  def self.stat_by_signed_on
    find_by_sql '
        select strftime("%Y",signed_on) as year,
                count(id) as projects_count, 
                sum(payed_total_amount) as payed_total_amount, 
                sum(paying_total_amount) as paying_total_amount, 
                sum(paused_total_amount) as paused_total_amount
        from projects group by strftime("%Y",signed_on)
        order by strftime("%Y",signed_on) desc
    '
  end
  
  def self.stat_by_payed_on
    find_by_sql [ '
        select strftime("%Y",payed_on) as year,
                count(project_id) as projects_count, 
                sum(should_be_payed) as payed_total_amount
        from stages 
        where pay_status = ?
        group by strftime("%Y",payed_on)
        order by strftime("%Y",payed_on) desc
    ', 'payed'
    ]
  end
  
end

